package com.woniuxy01;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;
import java.util.zip.CheckedOutputStream;

/**
 * @author 作者
 * @create 2025-08-08 14:32
 */
public class Test01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db2";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入你要查询的学号");
        String sname=sc.next();
//        String sql="select * from stu where sid="+sid;
        String sql="select * from stu where sname like '%"+sname+"%'";
        Statement statement = connection.createStatement();
        //executeQuery执行查询，返回值ResultSet，存储查询结果
        ResultSet resultSet = statement.executeQuery(sql);
        //元数据:描述数据的数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        for(int i=0;i<metaData.getColumnCount();i++){
            System.out.println(metaData.getColumnName(i+1));
        }


        //判断是否存在下一条数据，如果有同时读取一行数据，参数可以使用下标(JDBC下标从1开始)，也可以使用列名
        while (resultSet.next()) {
            //getXxx()获取当前行的某列数据
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getObject(3));
            System.out.println(resultSet.getObject(4));
            System.out.println("--------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test06() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db2";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入用户名");
        String sname = sc.nextLine();
        System.out.println("输入密码");
        String pwd = sc.nextLine();
        String sql="select * from stu where sname='"+sname+"' and password='"+pwd+"'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if(resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
    }

    private static void test05() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db2";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入你要查询的学号");
        String sname=sc.next();
//        String sql="select * from stu where sid="+sid;
        String sql="select * from stu where sname like '%"+sname+"%'";
        Statement statement = connection.createStatement();
        //executeQuery执行查询，返回值ResultSet，存储查询结果
        ResultSet resultSet = statement.executeQuery(sql);
        //判断是否存在下一条数据，如果有同时读取一行数据，参数可以使用下标(JDBC下标从1开始)，也可以使用列名
        while (resultSet.next()) {
            //getXxx()获取当前行的某列数据
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getInt(3));
            System.out.println("--------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test04() throws ClassNotFoundException, SQLException {
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db2";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from stu";
        Statement statement = connection.createStatement();
        //executeQuery执行查询，返回值ResultSet，存储查询结果
        ResultSet resultSet = statement.executeQuery(sql);
        //判断是否存在下一条数据，如果有同时读取一行数据，参数可以使用下标(JDBC下标从1开始)，也可以使用列名
        while (resultSet.next()) {
            //getXxx()获取当前行的某列数据
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getInt(3));
            System.out.println("--------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test03() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db2";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入学号");
        int sid = sc.nextInt();
        System.out.println("输入姓名");
        String sname = sc.next();
        System.out.println("输入年龄");
        int age = sc.nextInt();
        String sql="update stu set sname='"+sname+"' ,age="+age+" where sid="+sid;
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println(count);
        statement.close();
        connection.close();
    }

    private static void test02() {
        Connection connection = null;
        Statement statement = null;
        try {
            Scanner sc = new Scanner(System.in);
            //JDBC四大参数:核心类，连接协议，用户名，密码
            //核心类：com.mysql.jdbc.Driver或者com.mysql.cj.jdbc.Driver
            String className = "com.mysql.cj.jdbc.Driver";
            //连接参数"协议名称://IP地址:端口/数据库名" 连接本机的3306端口可以省略
            String url = "jdbc:mysql:///db2";
            String user = "root";
            String password = "123";
            //底层使用反射加载类:加载操作MySQL数据库的核心类，不写会自动加载
            Class.forName(className);
            //通过DriverManager类，创建连接对象：java程序和数据库直接的一条通道
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("输入学号");
            int sid = sc.nextInt();
            System.out.println("输入姓名");
            String sname = sc.next();
            System.out.println("输入年龄");
            int age = sc.nextInt();
            //定义sql语句
            String sql = "insert into stu values(" + sid + " '" + sname + "'," + age + ")";
            //创建Statement类的对象，执行sql
            statement = connection.createStatement();
            //增删改都用这个方法
            int count = statement.executeUpdate(sql);
            System.out.println("count=" + count);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }
    }

    private static void test01() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        //JDBC四大参数:核心类，连接协议，用户名，密码
        //核心类：com.mysql.jdbc.Driver或者com.mysql.cj.jdbc.Driver
        String className = "com.mysql.cj.jdbc.Driver";
        //连接参数"协议名称://IP地址:端口/数据库名" 连接本机的3306端口可以省略
        String url = "jdbc:mysql:///db2";
        String user = "root";
        String password = "123";
        //底层使用反射加载类:加载操作MySQL数据库的核心类，不写会自动加载
        Class.forName(className);
        //通过DriverManager类，创建连接对象：java程序和数据库直接的一条通道
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入学号");
        int sid = sc.nextInt();
        System.out.println("输入姓名");
        String sname = sc.next();
        System.out.println("输入年龄");
        int age = sc.nextInt();
        //定义sql语句
        String sql = "insert into stu values(" + sid + " '" + sname + "'," + age + ")";
        //创建Statement类的对象，执行sql
        Statement statement = connection.createStatement();
        //增删改都用这个方法
        int count = statement.executeUpdate(sql);
        System.out.println("count=" + count);
        statement.close();
        connection.close();
    }
}
